<?php
	defined( "AZ_EXE_" ) or die ( "CORE VARIABLE IS NOT DEFINED." );
	class ResourceModel extends Model {
		public function display( $start=0 , $perpage=20 , $searchString="" , $searchColumns = array("name") , $sortColumn="name" , $sortCriteria="ASC" ){
			$start = is_numeric($start)&&$start>-1?$start*$perpage:0;
			$perpage = is_numeric($perpage)&&$perpage>-1?$perpage:20;
			
			$result = array();
			$query = "SELECT resources.* , users.first_name as author , az_categories.title_en as category_name FROM resources , users , az_categories WHERE resources.user_id = users.id && az_categories.id = resources.menu_id " ;
			if ( $searchString != "" ){
				$searchColumns = ( $searchColumns===false || ( is_array($searchColumns) && empty($searchColumns) ) ) ? array("name") : $searchColumns ; 
				if ( is_array($searchColumns) && !empty($searchColumns) ){
				$conditions = "" ;
					$conditions = array() ;
					foreach( $searchColumns AS $columnName ){
						$conditions[] = " $columnName LIKE '%$searchString%' " ;
					}
					$conditions = " AND (" . implode("OR", $conditions ) . ") ";
					$sortColumn = $sortColumn===""||$sortColumn===false?"name":$sortColumn;
					$sortCriteria = $sortCriteria===""||$sortCriteria===false?"ASC":$sortCriteria;
					$query .= "$conditions " ;
				}
			}
			if( $sortColumn != "" || $sortColumn != false ){
				$sortCriteria = $sortCriteria!=""&&$sortCriteria!=false?$sortCriteria:"ASC";
				$query .= " ORDER BY $sortColumn $sortCriteria " ;
			}
			$this->queryGetRow = $query ;
			$query .= " LIMIT ".($start*$perpage)." , ".$perpage."; " ;
			//echo $query;
			//exit();
			$result = $this->executeQueryAsObject( $query ) ;
			return $result;
		}
		public function getTotalRecords(){
			return $this->getRowWithQuery($this->queryGetRow) ;
		}
		public function add( $user_id ,$caption, $name , $type ,$category, $size , $file_type , $description_kh, $description_en , $published ){
			$time = date("Y-m-d");
			$location = "resources";
			$description_kh = $this->sqlRealEscape($description_kh);
			$description_en = $this->sqlRealEscape($description_en);
			$name = $this->sqlRealEscape($name);
			return $this->executeQuery("INSERT INTO resources SET caption='$caption' ,name='$name' , location='$location' , type='$type' , menu_id ='$category' , size='$size' , file_type='$file_type' , user_id='$user_id' , description_kh='$description_kh' ,description_en='$description_en' , published='$published' , created='$time' , modified='$time' ;");
		}
		public function edit( $id , $user_id , $caption , $type , $category , $description_kh, $description_en , $published ){
			$caption = $this->sqlRealEscape($caption);
			$time = date("Y-m-d");
			$this->executeQuery("UPDATE resources SET type='$type' , caption = '$caption' , menu_id = '$category' , user_id='$user_id' , description_kh='$description_kh' ,description_en='$description_en' , published='$published' , modified='$time' WHERE id='$id' ;");
		}
		public function find($id){
			return $this->executeQueryAsObject("SELECT resources.* , users.first_name as author FROM resources , users WHERE resources.user_id = users.id AND resources.id='$id' ;");
		}
		public function getCategory($catId){
			$query = "SELECT * FROM az_categories where id = $catId ;" ;
			return $this->executeQueryAsObject($query);
		}
		public function getCategories($cate_id,$lang){
			$query = "SELECT c.id as cate_id, m.".($lang=="kh"?"name_kh":"name_en")." as menu_name, c.show_description, c.".($lang=="kh"?"title_kh":"title_en")." as cate_name,c.".($lang=="kh"?"description_kh":"description_en")." as cate_description, m.id as menu_id
					FROM az_categories as c INNER JOIN az_menu as m ON c.menu_id = m.id";
						
			if($cate_id != ""){
				$query .= " WHERE c.id ='" . $cate_id . "' AND c.published = 1";
			}else{
				$query .= " WHERE c.published = 1 AND m.alias = 'resource' 
						AND ((c.".($lang=="kh"?"description_kh":"description_en")." <> '' AND (SELECT COUNT(c2.id) FROM az_categories as c2 INNER JOIN resources as r on c2.id = r.menu_id) > 0)
							OR (c.".($lang=="kh"?"description_kh":"description_en")." = '' AND (SELECT COUNT(c2.id) FROM az_categories as c2 INNER JOIN resources as r on c2.id = r.menu_id WHERE c.id = c2.id) > 0))
						";
			}
			$query .= " GROUP BY c.ordering" ;
			return $this->executeQueryAsObject($query);
		}
		public function getPublished($type=array(2,3),$catId=0,$lang){
			$query = "SELECT resources.id, resources.menu_id,resources.caption , resources.name, resources.created,resources.type,resources.".($lang=="kh"?"description_kh":"description_en")." res_description,
					users.first_name as author , az_categories.".($lang=="kh"?"title_kh":"title_en")." as category_name, az_categories.".($lang=="kh"?"description_kh":"description_en")." as cate_description, az_categories.id as cate_id
					FROM resources , users , az_categories
					WHERE resources.user_id = users.id && az_categories.id = resources.menu_id  and resources.published=1" ;
			if ( count( $type ) > 0 ){
				$temp = array();
				foreach( $type as $t ){
					$temp[] = " resources.type='$t' ";
				}
				$query .= " AND ( ". implode("OR",$temp) ." ) ";
			}
			else{
				$query .= " AND ( type = 3 OR type = 2 ) "; 
			}
			if(isset($catId)&&$catId>0) $query .= " AND az_categories.id = $catId " ;
			//$query .= " ORDER BY az_categories.".($lang!="kh"?"title_en":"title_kh")." ASC " ;
			$query .= " ORDER BY resources.id DESC " ;
			//exit();
			return $this->executeQueryAsObject($query);
		}
		public function setPublished($id , $published){
			return $this->executeQuery("UPDATE resources SET published='$published' WHERE id = '$id' ; ");
		}

		public function delete($id){
			$this->executeQuery("DELETE FROM resources WHERE id='$id' ;");
		}
	}